package com.java.dao.impl;

import com.java.bean.User;
import com.java.dao.BaseUserDao;
import com.java.util.DruidUtil;
import com.java.util.DuplicateCodeException;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseUserDaoImpl implements BaseUserDao {
    public static final String SQL_CONSOLE = "SELECT " +
            "COUNT(ID) user_size," +
            "COUNT(TO_DAYS(REGTIME)=TO_DAYS(NOW()) OR NULL) user_day " +
            "FROM USER";
    public static final String SQL_FIND_ALL = "SELECT * FROM USER";
    /**
     * 分页查询数据库中所有用户信息
     */
    public static final String SQL_FIND_LIMIT = "SELECT * FROM USER LIMIT ?,?";
    /**
     * 通过用户名查询用户信息
     */
    public static final String SQL_FIND_BY_USER_NAME = "SELECT * FROM USER WHERE USERNAME=?";
    /**
     * 通过手机号查询用户信息
     */
    public static final String SQL_FIND_BY_USER_PHONE = "SELECT * FROM USER WHERE USERPHONE=?";
    /**
     * 根据身份证查询信息
     */
    public static final String SQL_FIND_BY_USER_ID_NUMBER = "SELECT * FROM USER WHERE USERIDNUMBER=?";
    /**
     * 录入用户
     */
    public static final String SQL_INSERT = "INSERT INTO USER(USERNAME,USERPHONE,USERIDNUMBER,PASSWORD,REGTIME,STATUS) VALUES(?,?,?,?,NOW(),0)";
    /**
     * 修改用户
     */
    public static final String SQL_UPDATE = "UPDATE USER SET USERNAME=?,USERPHONE=?,USERIDNUMBER=?,PASSWORD=? WHERE ID=?";
    /**
     * 修改用户登录时间
     */
    public static final String SQL_UPDATE_LOGIN_TIME = "UPDATE USER SET PRETIME=NOW() WHERE ID=?";
    /**
     * 快递员的删除
     */
    public static final String SQL_DELETE = "DELETE FROM USER WHERE ID=?";

    /**
     * 查询数据库中所有的用户数
     *
     * @return
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_CONSOLE);
            resultSet = statement.executeQuery();
            if(resultSet.next()){
                int user_size = resultSet.getInt("user_size");
                int user_day = resultSet.getInt("user_day");
                Map<String,Integer> data1 = new HashMap<>();
                data1.put("user_size",user_size);
                data1.put("user_day",user_day);
                data.add(data1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return data;
    }

    /**
     * 查询所有的用户列表
     *
     * @param limit      是否分页查询
     * @param offset     起始值
     * @param pageNumber 查询页数
     * @return
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            if(limit){
                statement = connection.prepareStatement(SQL_FIND_LIMIT);
                statement.setInt(1,offset);
                statement.setInt(2,pageNumber);
            }else {
                statement = connection.prepareStatement(SQL_FIND_ALL);
            }
            resultSet = statement.executeQuery();

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String userPhone = resultSet.getString("userPhone");
                String userIdNumber = resultSet.getString("userIdNumber");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                User u = new User();
                u.setId(id);
                u.setUsername(username);
                u.setUserPhone(userPhone);
                u.setUserIdNumber(userIdNumber);
                u.setPassword(password);
                u.setRegTime(regTime);
                u.setPreTime(preTime);
                data.add(u);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return data;
    }

    /**
     * 根据用户名查询
     *
     * @param userName
     * @return
     */
    @Override
    public List<User> findByName(String userName) {
        ArrayList<User> data = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_USER_NAME);
            statement.setString(1,userName);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String password = resultSet.getString("password");
                String userPhone = resultSet.getString("userPhone");
                String userIdNumber = resultSet.getString("userIdNumber");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                User e = new User(id,userName, password, userPhone, userIdNumber,regTime,preTime);
                data.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return data;
    }

    /**
     * 根据用户身份证查询
     *
     * @param userIdNumber
     * @return
     */
    @Override
    public User findByIdNumber(String userIdNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_USER_ID_NUMBER);
            statement.setString(1,userIdNumber);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String userPhone = resultSet.getString("userPhone");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                User e = new User(id, username,password, userPhone, userIdNumber,regTime,preTime);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    /**
     * 根据用户手机号拆查询
     *
     * @param userPhone
     * @return
     */
    @Override
    public User findByUserPhone(String userPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_USER_PHONE);
            statement.setString(1,userPhone);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String userIdNumber = resultSet.getString("userIdNumber");
                Timestamp regTime = resultSet.getTimestamp("regTime");
                Timestamp preTime = resultSet.getTimestamp("preTime");
                User u = new User();
                u.setId(id);
                u.setUsername(username);
                u.setUserPhone(userPhone);
                u.setUserIdNumber(userIdNumber);
                u.setPassword(password);
                return u;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    /**
     * 用户的录入
     *
     * @param user
     * @return
     */
    @Override
    public boolean insert(User user) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.    填充参数
            state.setString(1,user.getUsername());
            state.setString(2,user.getUserPhone());
            state.setString(3,user.getUserIdNumber());
            state.setString(4,user.getPassword());
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用户信息修改
     *
     * @param id
     * @param newUser
     * @return
     */
    @Override
    public boolean update(int id, User newUser) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,newUser.getUsername());
            state.setString(2,newUser.getUserPhone());
            state.setString(3,newUser.getUserIdNumber());
            state.setString(4,newUser.getPassword());
            state.setInt(5,id);
            return state.executeUpdate() > 0;
        }catch (SQLException throwables){
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 更新登录时间
     *
     * @param id
     * @return
     */
    @Override
    public boolean updateLoginTime(int id) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQL_UPDATE_LOGIN_TIME);
            statement.setInt(1,id);
            return statement.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }

    /**
     * 根据id删除用户信息
     *
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQL_DELETE);
            statement.setInt(1,id);
            return statement.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }
}
